package com.huike.report.mapper;

import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.List;

import com.github.pagehelper.Page;
import com.huike.business.domain.TbBusiness;
import com.huike.clues.domain.TbActivity;
import com.huike.clues.domain.TbAssignRecord;
import com.huike.clues.domain.TbClue;
import com.huike.clues.domain.vo.ClueReportVo;
import com.huike.contract.domain.TbContract;
import com.huike.report.domain.ChannelStatistics;
import com.huike.report.domain.OwnerShipStatstics;
import com.huike.report.domain.dto.StatisticsPageDTO;
import com.huike.report.domain.DeptStatistics;
import com.huike.report.domain.vo.ActivityStatistics;
import com.huike.report.domain.vo.ActivityStatisticsVo;
import com.huike.report.domain.vo.ChannelStatisticsVO;
import com.huike.report.domain.vo.SubjectStatisticsVO;
import org.apache.ibatis.annotations.Mapper;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

/**
 * 首页统计分析的Mapper
 * @author Administrator
 *
 */
@Mapper
public interface ReportMapper {


    /**=========================================基本数据========================================*/


    Integer getUserCount(@Param("beginTime") LocalDateTime beginTime,@Param("endTime") LocalDateTime endTime);

    @Select("select sd.dept_name dept_name,round(sum(td.contract_order),2) total_amount,count(*) num,td.dept_id dept_id from tb_contract td,sys_dept sd " +
            "where sd.dept_id = td.dept_id and date_format(td.create_time,'%y%m%d') >= date_format(#{beginCreateTime},'%y%m%d') and date_format(td.create_time,'%y%m%d') <= date_format(#{endCreateTime},'%y%m%d') " +
            "group by td.dept_id order by total_amount desc,num desc")
    Page<DeptStatistics> deptStatistics(StatisticsPageDTO dto);

    @Select("select tc.create_by create_by,round(sum(contract_order),2) total_amount,count(*) num from tb_contract tc where date_format(tc.create_time,'%y%m%d') >= date_format(#{beginCreateTime},'%y%m%d') and date_format(tc.create_time,'%y%m%d') <= date_format(#{endCreateTime},'%y%m%d') " +
            "group by tc.create_by order by total_amount desc,num desc")
    Page<OwnerShipStatstics> ownerShipStatistics(StatisticsPageDTO dto);

    @Select("select if(tc.channel=0,'线上活动','推广介绍') channel,round(sum(contract_order),2) total_amount,count(*) num from tb_contract tc where date_format(tc.create_time,'%y%m%d') >= date_format(#{beginCreateTime},'%y%m%d') and date_format(tc.create_time,'%y%m%d') <= date_format(#{endCreateTime},'%y%m%d') " +
            "group by tc.channel order by total_amount desc,num desc")
    Page<ChannelStatistics> channelStatistics(StatisticsPageDTO dto);

    @Select("select sum(if(contract.name is null,0,1)) num, sdd.dict_label subject from tb_contract contract inner join tb_course course on contract.course_id = course.id " +
            "right join sys_dict_data sdd on course.subject = sdd.dict_value " +
            "where sdd.dict_type = 'course_subject' " +
            "and date_format(contract.create_time,'%y%m%d') >= date_format(#{beginCreateTime},'%y%m%d') " +
            "and date_format(contract.create_time,'%y%m%d') <= date_format(#{endCreateTime},'%y%m%d') " +
            "group by sdd.dict_label")
    List<SubjectStatisticsVO> subjectStatistics(@Param("beginCreateTime")LocalDate beginCreateTime,@Param("endCreateTime")LocalDate endCreateTime);

    List<ClueReportVo> getClueReportPages(@Param("beginCreateTime") LocalDateTime beginCreateTime, @Param("endCreateTime") LocalDateTime endCreateTime, @Param("id") Long id, @Param("owner") String owner);

    TbBusiness gettbBusinessByClueId(@Param("clueId") Long clueId);

    TbActivity gettbActivityByClueId(@Param("clueId") Long clueId);
    TbContract gettbContractByBusinessId(@Param("businessId")Long businessId);

    List<ChannelStatisticsVO> getchannel(@Param("beginTime")LocalDateTime beginTime,@Param("endTime")LocalDateTime endTime);
    List<ActivityStatistics> getactivity(@Param("beginTime")LocalDateTime beginTime,@Param("endTime")LocalDateTime endTime);

    List<ActivityStatisticsVo> getactivityvos(@Param("beginCreateTime") LocalDateTime beginCreateTime,@Param("endCreateTime") LocalDateTime endCreateTime,@Param("name") String name,@Param("code") String code);


    Integer cluesNum(@Param("activityId")Long activityId);
    Integer getCluesNum(@Param("activityId")Long activityId,@Param("status")Integer status);

    @Select("select id  from huike.tb_activity where code = #{code}")
    Long getTbactivityCode(@Param("code")String code);

    Integer amount(@Param("activiId")Long activiUd);
    Integer cost(@Param("activiId")Long activiUd);
    TbAssignRecord getTbAssignRecordByClueId(@Param("clueId")Long clueId,@Param("createBy") String createBy);

    @Select("select dept_name from huike.sys_dept where dept_id = #{deptId}")
    String  getDeptName(@Param("deptId")Long deptId);
    /**=========================================今日简报========================================*/



	/**=========================================待办========================================*/

}
